#!/usr/bin/env python

# Volturnus Address Book v2 svn

# -----------------------------------------------------------------------
# Copyright (C) 2008 Bryan Smith

# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
# -----------------------------------------------------------------------

# -----------------------------------------------------------------------
# Thanks
#   docs.python.org
#   www.w3schools.com
#   Richard Hummel
# -----------------------------------------------------------------------

# -----------------------------------------------------------------------
# To Do
#   - Exceptions on SQL queries
#   - Comment...desperately.
#   - Add more remote methods
# -----------------------------------------------------------------------

import sqlite3
import sys
import socket
from xmlrpclib import ServerProxy
import config

# Main Volturnus class
# Contains methods to interact with database
class Volturnus:

    host = socket.gethostname()
    server = ""
    connected = False
    # Constructor
    # Prints welcome message and establishes connection with database
    def __init__(self):
        # Exception is thrown if connection can not be made
        try:
            print "==========================\n" + \
                  "| Volturnus Address Book |\n" + \
                  "==========================\n\n" + \
                  "Written by Bryan Smith. (c) 2008 Bryan Smith.\n" + \
                  "Type help for a list of possible commands"
            self.con = sqlite3.connect("volturnus.db") # Connect to the database
            self.cur = self.con.cursor() # Create a cursor
        except Exception:
            print "Error establishing connection to database."

    # Sort the names in the database by last name
    def sortNames(self):
        try:
            sort_query = "SELECT * FROM voldb ORDER BY lname" # SQL query for sorting by last_name
            self.cur.execute(sort_query) # Execute the query
            self.con.commit() # Commit the changes
        except Exception:
            print "Error occurred sorting names."
    # Gets the input from the user and calls the appropriate method
    def getInput(self):
        opt = raw_input("\n:> ").lower() # Print out the command prompt for the user
        if opt == "add":
            self.add_name() # Add name
        elif opt == "del":
            self.delete_name() # Delete name
        elif opt == "edit":
            self.edit_name() # Edit name
        elif opt == "view":
            self.view_all_names() # View names
        elif opt == "looklast":
            self.lookup_last_name() # Lookup by last name
        elif opt == "lookcity":
            self.lookup_city_town() # Lookup by town/city
        elif opt == "exhtml":
            self.export_to_html() # Export info to html doc
        elif opt == "excsv":
            self.export_to_csv() # Export to CSV (Comma Separated Values)
        elif opt == "rconnect":
            self.remote_connect()
        elif opt == "rview":
            self.remote_view()
        elif opt == "rview_last":
            self.remote_view_last()
        elif opt == "help":
            self.help_user() # Help
        elif opt == "about":
            self.about() # Print about info
        elif opt == "exit":
            self.con.close() # Close the database
            sys.exit # Exit
        else:
            print "Invalid option. Type help for a listing of commands."
            self.getInput()

    def add_name(self):
        fname = raw_input("First Name:> ")
        lname = raw_input("Last Name:> ")
        street = raw_input("Street:> ")
        town = raw_input("City/Town:> ")
        province = raw_input("Province/State:> ")
        postal = raw_input("Postal/Zip Code:> ")
        country = raw_input("Country:> ")
        homephone = raw_input("Home Phone Number:> ")
        workphone = raw_input("Work Phone Number:> ")
        try:
            response = "INSERT INTO voldb VALUES('" + lname + "','" + fname + "','" + street + \
                           "','" + town + "','" + province + "','" + postal + \
                           "','" + country + "','" + homephone + "','" + workphone + "')"
                # insert the info into the database using an sql query
            self.cur.execute(response)
            self.con.commit() # commit the contact info to the database
        except Exception:
            print "Insertion failed. Check write permissions."
        self.getInput()
        
    def delete_name(self):
        del_fname = raw_input("First Name:> ")
        del_lname = raw_input("Last Name:> ")
        try:
            del_info = "SELECT * from voldb"
            self.cur.execute(del_info)
        except Exception:
            print "Error occurred collecting names from database."
        full_name = del_fname + del_lname # add the two names together
        for info in self.cur.fetchall():
            del_name = info[1] + info[0] # add the two names together (for comparison to full_name)
            if full_name.lower() == del_name.lower(): # Make both lower case for better comparison (ignoring case)
                # Print the name to show who should be deleted (in case it was a mistake)
                print "\n" + info[0] + ", " + info[1]
                print info[2]
                print info[3] + ", " + info[4] + ", " + info[6]
                print info[5]
                print "Home: " + info[7]
                print "Work: " + info[8]
                sure = raw_input("\nAre you sure you want to delete this person (y/n)? ") # ask for confirmation from the user
                if sure.lower() == 'y':
                    try:
                        self.cur.execute("DELETE FROM voldb WHERE lname = '" + info[0] + "'" + "AND fname ='" + info[1] + "'")
                        self.con.commit() # delete the contact and commit the changes to the sqlite database
                    except StandardError:
                        print "Insertion failed. Check write permissions."
                    print "Contact deleted."
                elif sure.lower() == 'n': # In case the individual changes their mind or selects wrong individual
                    print "Contact retained."
                else:
                    print "Invalid option."
        self.getInput()
        
    def edit_name(self):
        edit_fname = raw_input("First Name:> ")
        edit_lname = raw_input("Last Name:> ")
        try:
            edit_info = "SELECT * from voldb"
            self.cur.execute(edit_info)
        except Exception:
            print "Error occurred collecting names from database."
        full_name = edit_fname + edit_lname # add the two names together
        for info in self.cur.fetchall():
            edit_name = info[1] + info[0] # add the two names together (for comparison to full_name)
            if full_name.lower() == edit_name.lower():
                print "What piece of information would you like to change for: \n"
                print info[0] + ", " + info[1]
                print info[2]
                print info[3] + ", " + info[4] + ", " + info[6]
                print info[5]
                print "Home: " + info[7]
                print "Work: " + info[8] + "\n"

                print "First Name (a), Last Name (b), Street (c), City/Town (d), Province/State (e), Postal/Zip Code (f), Country (g), Home Phone (h), Work Phone (i)"
                # ask the user which piece of info they would like to change

                edit_opt = raw_input(":> ")
                # update each element according to the user input
                try:
                    if edit_opt == 'a':
                        new_name = raw_input("New First Name :> ")
                        self.cur.execute("UPDATE voldb SET fname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'b':
                        new_name = raw_input("New Last Name :> ")
                        self.cur.execute("UPDATE voldb SET lname = '" + new_name + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'c':
                        new_street = raw_input("New Street :> ")
                        self.cur.execute("UPDATE voldb SET street = '" + new_street + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'd':
                        new_town = raw_input("New City/Town :> ")
                        self.cur.execute("UPDATE voldb SET city_town = '" + new_town + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'e':
                        new_province = raw_input("New Province/State :> ")
                        self.cur.execute("UPDATE voldb SET province_state = '" + new_province + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'f':
                        new_postal = raw_input("New Postal/ZIP Code :> ")
                        self.cur.execute("UPDATE voldb SET postal_zip = '" + new_postal + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'g':
                        new_country = raw_input("New Country :> ")
                        self.cur.execute("UPDATE voldb SET country = '" + new_country + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'h':
                        new_homephone = raw_input("New Home Phone Number :> ")
                        self.cur.execute("UPDATE voldb SET homephone = '" + new_homephone + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    elif edit_opt == 'i':
                        new_workphone = raw_input("New Work Phone Number :> ")
                        self.cur.execute("UPDATE voldb SET workphone = '" + new_workphone + "' WHERE fname = '" + info[1] + "'" + "AND lname = '" + info[0] + "'")
                        self.con.commit() # commit the new information
                    else:
                        print "Invalid option."
                except Exception:
                    print "Error changing information. Please try again."                
        self.getInput()
        
    def view_all_names(self):
        try:
            get_info = "SELECT * from voldb"
            self.cur.execute(get_info)
            self.sortNames() # sort the users before printing them
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.cur.fetchall():
            print info[0] + ", " + info[1] + ": " + info[2] + ", " + info[3] + ", " + info[4] + ", " + info[6] + ", " + info[5]
            print "Home: " + info[7] + ", Work: " + info[8] + "\n"
        self.getInput()
        
    def lookup_last_name(self):
        last_found = False
        last_name = raw_input("Search Query: ")
        try:
            get_info = "SELECT * from voldb WHERE lname like '%" + last_name + "%'"
            self.cur.execute(get_info)
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.cur.fetchall():
            last_found = True
            print info[0] + ", " + info[1] + ": " + info[2] + ", " + info[3] + ", " + info[4] + ", " + info[6] + ", " + info[5]
            print "Home: " + info[7] + ", Work: " + info[8] + "\n"
        if last_found == False:
            print "Name not found."
        self.getInput()
        
    def lookup_city_town(self):
        citytown_found = False
        citytown = raw_input("Search Query: ")
        try:
            get_info = "SELECT * from voldb WHERE city_town like '%" + citytown + "%'"
            self.cur.execute(get_info)
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.cur.fetchall():
            citytown_found = True
            print info[0] + ", " + info[1] + ": " + info[2] + ", " + info[3] + ", " + info[4] + ", " + info[6] + ", " + info[5]
            print "Home: " + info[7] + ", Work: " + info[8] + "\n"
        if citytown_found == False:
            print "City/Town not found."
        self.getInput()

    def export_to_html(self):
        # page variable will hold html contents - as it is here, it holds the beginning of the document
        page = """
<html>
<title>Volturnus Contact List</title>
<center>
<h2>Contacts</h2>
<hr>
<table border=1 cellspacing=0>
<tr bgcolor=#008000>
<td><font color=#ffffff><center>Last Name</center></font></td>
<td><font color=#ffffff><center>First Name</center></font></td>
<td><font color=#ffffff><center>Street</center></font></td>
<td><font color=#ffffff><center>City/Town</center></font></td>
<td><font color=#ffffff><center>Province/State</center></font></td>
<td><font color=#ffffff><center>Postal/Zip Code</center></font></td>
<td><font color=#ffffff><center>Country</center></font></td>
<td><font color=#ffffff><center>Home Phone Number</center></font></td>
<td><font color=#ffffff><center>Work Phone Number</center></font></td>
</tr>
""" # this will hold the contents of the page
        print "Collecting data..."
        try:
            page_info = "SELECT * from voldb"
            self.cur.execute(page_info)
            self.sortNames() # Sort the names before outputting them to a table
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.cur.fetchall():
            page += "<tr bgcolor=#3deb3d>\n<td>" + info[0] + "</td>\n<td>" + info[1] + "</td>\n<td>" + info[2] +  \
                    "</td>\n<td>" + info[3] +"</td>\n<td>" + info[4] + "</td>\n<td>" + info[5] + \
                    "</td>\n<td>" + info[6] + "</td>\n<td>" + info[7] + "</td>\n<td>" + info[8] +"</td>\n</tr>\n" # add the data to a row on the table
        page+= """</table>
<hr>
<font size=2>Table constructed using <a href="http://code.google.com/p/volturnus">Volturnus Address Book</a>.
</font>
</center>
</html>
""" # finish off the page
        try:
            output = open('Volturnus Contacts.html', 'w')
            output.write(page) # write the string (page) with the html to contact_output.html for viewing outside of volturnus
            output.close()
            print "Contacts written to 'Volturnus Contacts.html'"
        except IOError:
            print "Can not write to file. Check permissions."
        self.getInput()
        
    def export_to_csv(self):
        csv_contents = ""
        print "Collecting data..."
        try:
            page_info = "SELECT * from voldb"
            self.cur.execute(page_info)
            self.sortNames() # Sort the names before outputting them to csv
        except StandardError:
            print "Retrieval failed. Check read permissions."
        for info in self.cur.fetchall():
            csv_contents += "\n" + info[1] + "," + info[0] + "," + info[1] + " " + info[0] + ",,,," + info[8] \
                             + "," + info[7] + ",,,," + info[2] + "," + info[3] + "," + info[4] + "," + info[5] \
                             + "," + info[6] + ",,,,,,,,,,,,,,,,,,,"
        try:
            output = open('Volturnus Contacts.csv', 'w')
            output.write(csv_contents) # write the string (page) with the csv to Volturnus Contacts.csv for use in alternate address books
            output.close()
            print "Contacts written to 'Volturnus Contacts.csv'"
        except IOError:
            print "Can not write to file. Check permissions."
        self.getInput()

    def remote_connect(self):
        saddr = config.server_address
        serv_name = ""
        if saddr == "":
            serv_name = raw_input("Server Address: ")
        else:
            serv_name = saddr
        try:
            self.server = ServerProxy("http://" + serv_name + ":9876")
            print self.server.establish_connection(self.host)
            self.connected = True
        except Exception:
            print "Error communicating with VolServer"
        self.getInput()
        
    def remote_view(self):
        if self.connected == True:
            try:
                print self.server.remote_view_names()
            except Exception:
                print "Error communicating with VolServer"
        else:
            print "Not connected to remote server."
        self.getInput()
        
    def remote_view_last(self):
        if self.connected == True:
            name = raw_input("Search Query: ")
            try:
                print self.server.remote_view_lastname(name)
            except Exception:
                print "Error communicating with VolServer"
        else:
            print "Not connected to remote server."
        self.getInput()

    def help_user(self):
        print """
        =================================================
            Commands                                      

            ----- Local Commands -----
            add - Add a contact to the database
            del - Delete a contact in the database
            edit - Edit a contact in the database
            view - View all contacts in the database
            looklast - Lookup a contact by last name
            lookcity - Lookup a contact by city/town
            exhtml - Export database to HTML
            excsv - Export database to CSV
            
            ----- Remote Commands -----
            rconnect - Connect to remote VolServer
            rview - View contacts on remote VolServer
            rview_last - Viwe contacts by last name on remote VolServer
            
            ----- Misc -----
            about - About the program
            exit - Exit the program
        ================================================="""
        self.getInput()

    def about(self):
        print "\n== Volturnus Address Book =="
        print "Written by Bryan Smith. (c) 2008 Bryan Smith."
        print "Licensed under the GPL v.2 (GNU General Public License version 2)."
        print "To view the license, read the included LICENSE file."
        self.getInput()

if __name__ == "__main__":
    v = Volturnus()
    v.getInput()